IF OBJECT_ID('ssf.gbl_semaphore_connection') IS NOT NULL
	DROP TABLE ssf.gbl_semaphore_connection
GO

IF OBJECT_ID('ssf.gbl_variable') IS NOT NULL
	DROP TABLE ssf.gbl_variable
GO

CREATE TABLE ssf.gbl_variable (
	variable_id INT IDENTITY(1, 1) NOT NULL
	, variable_name NVARCHAR(128) NOT NULL
	, variable_description NVARCHAR(2048) NULL
	, variable_value NVARCHAR(MAX) NULL
	, modified_date DATETIME2(7) DEFAULT SYSDATETIME() NOT NULL
)
GO

CREATE TABLE ssf.gbl_semaphore_connection (
	semaphore_connection_id INT IDENTITY(1, 1) NOT NULL
	, semaphore_name VARCHAR(128) NOT NULL
	, connection_id INT NOT NULL
	, locked_by_spid INT NULL
	, locked_by_user NVARCHAR(128) NULL
	, modified_date DATETIME2(7) DEFAULT SYSDATETIME() NOT NULL
)
GO

ALTER TABLE ssf.gbl_variable
ADD CONSTRAINT PK_gbl_variable
PRIMARY KEY CLUSTERED (variable_id)
GO

ALTER TABLE ssf.gbl_variable
ADD CONSTRAINT UQ_gbl_variable
UNIQUE (variable_name)
GO

ALTER TABLE ssf.gbl_semaphore_connection
ADD CONSTRAINT PK_gbl_semaphore_connection
PRIMARY KEY CLUSTERED (semaphore_connection_id)
GO

ALTER TABLE ssf.gbl_semaphore_connection
ADD CONSTRAINT UQ_gbl_semaphore_connection
UNIQUE (semaphore_name, connection_id)
GO
